package cn.qzbook.dao;


import cn.qzbook.domain.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDaoJDBC {

    private String driver = "com.mysql.jdbc.Driver";
    private String url = "jdbc:mysql://localhost:3306/school?characterEncoding=utf8";
    private String userName = "root";
    private String password = "qzbook";


    /**
     * 基于JDBC的方式动态拼接SQL
     * */
    public List<User> selectUser(Integer id, String name){
        List<User> users = new ArrayList<>();
        try {
            Class.forName(driver);

            Connection connection = DriverManager.getConnection(url, userName, password);

            // 【1 = 1】条件永远成立，所以在这条语句中不考虑加不加where关键字 只根据有没有参数 来添加加and关键字
            //两个参数都不存在，则查询所有
            StringBuffer sql = new StringBuffer("select * from `user` where 1 = 1");
            if (null != id){
                sql.append(" and id = ");
                sql.append(id);
            }
            if (null != name && !"".equals(name)){
                sql.append(" and name = '");
                sql.append(name);
                sql.append("'");
            }
            sql.append(";");
            System.out.println(sql);

            PreparedStatement preparedStatement = connection.prepareStatement(sql.toString());
            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()){
               User user = new User();
               user.setId(resultSet.getInt("id"));
               user.setName(resultSet.getString("name"));
               user.setSex(resultSet.getString("sex"));
               user.setAge(resultSet.getInt("age"));
               user.setHeight(resultSet.getFloat("height"));
               user.setWeight(resultSet.getFloat("weight"));
               user.setPhoneNum(resultSet.getLong("phoneNum"));
               users.add(user);
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return users;
    }

}
